package com.owenobyrne.btce;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.owenobyrne.btce.api.model.Trade;

public class DatabaseObject {

	
	static String user = "robin";
	static String password = "robin";
	static String url = "jdbc:mysql://10.34.130.59:3306/litecoin";
	static String driver = "com.mysql.jdbc.Driver";

	
	public static List<Trade> listTrade() {

		List<Trade> list = new ArrayList<Trade>();
		
		// String driver = "org.gjt.mm.mysql.Driver";
		// String tableName = "studinfo";
		String sqlstr;
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, user, password);
			// stmt = con.createStatement();

			
			
//			sqlstr = "select date,sum(price*amount)/sum(amount) price ,sum(amount) amount from trade group by date order by date desc limit 10000  ";
			
			sqlstr = "select STR_TO_DATE( FROM_UNIXTIME( DATE ) ,  '%Y-%m-%d %H:%i' ) as date,sum(price*amount)/sum(amount) price ,sum(amount) amount from trade group by STR_TO_DATE( FROM_UNIXTIME( DATE ) ,  '%Y-%m-%d %H:%i' )			order by date desc limit 1000";
			
			
			
			PreparedStatement prepareStatement = con.prepareStatement(sqlstr);

			ResultSet executeQuery = prepareStatement.executeQuery();
			while (executeQuery.next()) {
				Trade t = new Trade();
				t.setDate(executeQuery.getInt("date"));
				t.setPrice(executeQuery.getDouble("price"));
				t.setAmount(executeQuery.getDouble("amount"));
				list.add(t);
			}
		} catch (ClassNotFoundException e1) {
			System.out.println(e1.toString());
		} catch (SQLException e2) {
			System.out.println(e2.toString());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				System.out.println(e.toString());
			}
		}
		return list;
	}


	
	public static int maxTradeId(String tablename) {


		// String driver = "org.gjt.mm.mysql.Driver";
		// String tableName = "studinfo";
		String sqlstr;
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		int cnt=1;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, user, password);
			// stmt = con.createStatement();

			sqlstr = "select max(tid) as tid from    "+tablename;
			PreparedStatement prepareStatement = con.prepareStatement(sqlstr);

			ResultSet executeQuery = prepareStatement.executeQuery();
			
			if(executeQuery.next()) {
				cnt=executeQuery.getInt("tid");
			}
		} catch (ClassNotFoundException e1) {
			System.out.println(e1.toString());
		} catch (SQLException e2) {
			System.out.println(e2.toString());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				System.out.println(e.toString());
			}
		}
		return cnt;
	}

	public static int countTrade() {


		// String driver = "org.gjt.mm.mysql.Driver";
		// String tableName = "studinfo";
		String sqlstr;
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		int cnt=0;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, user, password);
			// stmt = con.createStatement();

			sqlstr = "select count(1) as cnt from trade   ";
			PreparedStatement prepareStatement = con.prepareStatement(sqlstr);

			ResultSet executeQuery = prepareStatement.executeQuery();
			
			if(executeQuery.next()) {
				cnt=executeQuery.getInt("cnt");
			}
		} catch (ClassNotFoundException e1) {
			System.out.println(e1.toString());
		} catch (SQLException e2) {
			System.out.println(e2.toString());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				System.out.println(e.toString());
			}
		}
		return cnt;
	}

	public static void log(String desc) {
		DatabaseObject.log("-",desc);
	}
	public static void log(String name,String desc) {

		// String driver = "org.gjt.mm.mysql.Driver";
		// String tableName = "studinfo";
		String sqlstr;
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, user, password);
			// stmt = con.createStatement();

			sqlstr = "INSERT INTO `log`(`name`, `desc`, `time`) values(?,?,now())";
			PreparedStatement prepareStatement = con.prepareStatement(sqlstr);
			// prepareStatement.setDate(1, (java.sql.Date) new Date());
			prepareStatement.setString(1, name);
			prepareStatement.setString(2, desc);
			
			prepareStatement.execute();
		} catch (ClassNotFoundException e1) {

			System.out.println(e1.toString());
		} catch (SQLException e2) {

			System.out.println(e2.toString());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				System.out.println(e.toString());
			}
		}
	}
	
	

	public static void insertRecord(int date, double price, double amount,
			int tid, String type,String tablename) {
		// String driver = "org.gjt.mm.mysql.Driver";
		// String tableName = "studinfo";
		String sqlstr;
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, user, password);
			// stmt = con.createStatement();

			sqlstr = "INSERT INTO `"+tablename+"`(`date`, `price`, `amount`, `tid`, `type`) values(from_unixtime(?),?,?,?,?)";
			PreparedStatement prepareStatement = con.prepareStatement(sqlstr);
			// prepareStatement.setDate(1, (java.sql.Date) new Date());
			prepareStatement.setInt(1, date);
			prepareStatement.setDouble(2, price);
			prepareStatement.setDouble(3, amount);
			prepareStatement.setInt(4, tid);
			prepareStatement.setString(5, type);
			prepareStatement.execute();
		} catch (ClassNotFoundException e1) {

			System.out.println(e1.toString());
		} catch (SQLException e2) {

			System.out.println(e2.toString());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				System.out.println(e.toString());
			}
		}
	}

	public static int insertRecordInBatch(List<Map<String, Object>> tradeList,String tableName,int tid) {
		// String driver = "org.gjt.mm.mysql.Driver";
		// String tableName = "studinfo";
		String sqlstr;
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
//		int tid=0;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, user, password);
			// stmt = con.createStatement();
			sqlstr = "INSERT INTO `"+tableName+"`(`date`, `price`, `amount`, `tid`, `type`) values(from_unixtime(?),?,?,?,?)";
			
			PreparedStatement prepareStatement = con.prepareStatement(sqlstr);
			for(Map<String, Object> map:tradeList ){
				
				int date = Integer.valueOf(""+map.get("date"));
				double price =Double.valueOf(""+map.get("price"));
				double amount =Double.valueOf(""+map.get("amount"));
				tid =Integer.valueOf(""+map.get("tid"));
				String type =""+map.get("type");
				
				prepareStatement.setInt(1, date);
				prepareStatement.setDouble(2, price);
				prepareStatement.setDouble(3, amount);
				prepareStatement.setInt(4, tid);
				prepareStatement.setString(5, type);
				
				
				prepareStatement.addBatch();
				
							
			}
			prepareStatement.executeBatch();
		
			// prepareStatement.setDate(1, (java.sql.Date) new Date());
			
//			prepareStatement.execute();
		} catch (ClassNotFoundException e1) {

			System.out.println(e1.toString());
		} catch (SQLException e2) {

			System.out.println(e2.toString());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				System.out.println(e.toString());
			}
		}
		return tid;
	}
	
	
	public static void insertRecord2(int date, double price, double amount,
			int tid, String type) {
		String user = "robin";
		String password = "123456";
		String url = "jdbc:mysql://robinhood.mysql.rds.aliyuncs.com:3306/litecoin";
		String driver = "com.mysql.jdbc.Driver";
		// String driver = "org.gjt.mm.mysql.Driver";
		// String tableName = "studinfo";
		String sqlstr;
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, user, password);
			// stmt = con.createStatement();

			sqlstr = "INSERT INTO `trade1`(`date`, `price`, `amount`, `tid`, `type`) values(?,?,?,?,?)";
			PreparedStatement prepareStatement = con.prepareStatement(sqlstr);
			// prepareStatement.setDate(1, (java.sql.Date) new Date());
			prepareStatement.setInt(1, date);
			prepareStatement.setDouble(2, price);
			prepareStatement.setDouble(3, amount);
			prepareStatement.setInt(4, tid);
			prepareStatement.setString(5, type);
			prepareStatement.execute();
		} catch (ClassNotFoundException e1) {

			System.out.println(e1.toString());
		} catch (SQLException e2) {

			System.out.println(e2.toString());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				System.out.println(e.toString());
			}
		}
	}
	
	public static void update(String sql) {

		// String driver = "org.gjt.mm.mysql.Driver";
		// String tableName = "studinfo";
		String sqlstr;
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, user, password);
			// stmt = con.createStatement();

			sqlstr = sql;
			PreparedStatement prepareStatement = con.prepareStatement(sqlstr);
			
			prepareStatement.execute();
		} catch (ClassNotFoundException e1) {

			System.out.println(e1.toString());
		} catch (SQLException e2) {

			System.out.println(e2.toString());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				System.out.println(e.toString());
			}
		}
	}
	
}